﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Web.Services;
using Microsoft.ApplicationServer.Caching;
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.ServiceRuntime;
using Microsoft.WindowsAzure.StorageClient;
using System.IO;
using System.Net.Mail;
using System.Collections;
using System.Net;

namespace WebRole1
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "taraService" in code, svc and config file together.
    public class taraService : ItaraService
    {
        public void DoWork()
        {
        }

        #region cache
        //static DataCacheFactory _Factory = new DataCacheFactory();

        //public bool initCache()
        //{
        //    bool success = false;
        //    var cache = _Factory.GetDefaultCache();
        //    patient cl = cache["patientList"] as patient;
        //    if (cl == null)
        //    {
        //        SqlOpenConnection();
        //        SqlCommand comm = SqlCommandConnection();

        //        try
        //        {
        //            comm.CommandText = "SELECT * FROM patient";
        //            List<patient> patientList = new List<patient>();
        //            SqlDataReader dr = comm.ExecuteReader();
        //            while (dr.Read())
        //            {
        //                patientList.Add(new patient
        //                {
        //                    Address1 = dr["Address1"].ToString(),
        //                    Address2 = dr["Address2"].ToString(),
        //                    Doc = dr["doctor"].ToString(),
        //                    FirstName = dr["FirstName"].ToString(),
        //                    LastName = dr["LastName"].ToString(),
        //                    Postcode = dr["Postcode"].ToString(),
        //                    Uid = Convert.ToInt16(dr["uid"].ToString()),
        //                    DateOfBirth = Convert.ToDateTime(dr["dateOfBirth"].ToString()),
        //                    Email = dr["email"].ToString(),
        //                    Gender = dr["gender"].ToString(),
        //                    ContactNumHome = dr["contactNumHome"].ToString(),
        //                    Title = dr["title"].ToString(),
        //                    ContactNumWork = dr["contactNumWork"].ToString(),
        //                    Country = dr["country"].ToString(),
        //                });
        //            }
        //            success = true;
        //            cache["patientList"] = patientList;
        //        }
        //        catch (SqlException) { }
        //        finally { SqlCloseConnection(); }
        //    }
        //    return success;
        //}
        #endregion

        # region SQL common methods

        //crash when using acs
        //public static string conString = ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
        //public static string conString = "Server=localhost; Database=elderGuardian; Integrated Security= true;";
        public static string conString = "Data Source=xwizp2ppu2.database.windows.net;Initial Catalog=taraDB;Persist Security Info=True;User ID=kyzer@xwizp2ppu2;Password=Iamhere0;Pooling=false";
        public SqlConnection conn = null;

        private SqlCommand SqlCommandConnection()
        {
            SqlCommand sql = new SqlCommand();
            sql.Connection = conn;
            return sql;
        }

        private void SqlOpenConnection()
        {
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = conString;
                conn.Open();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            catch (Exception)
            {
                SqlConnection.ClearAllPools();
            }
        }

        private void SqlCloseConnection()
        {
            conn.Close();
            conn.Dispose();
        }

        # endregion

        #region login
        // max time to authenticate = (maxmin+1)*60-1 seconds
        const int maxmin = 0;

        public string GetToken()
        {
            string ToHash, sResult;
            DateTime dt = DateTime.Now;
            ToHash = dt.ToString("yyyyMMdd") + "|" + dt.ToString("HHmm");
            sResult = Hash(ToHash);
            return sResult;
        }

        #region Doc

        public doctor DocUseService(string Key)
        {
            string[] HashArray;

            // Key string: HASH|User|OptionalData
            HashArray = Key.Split('|');

            if (DocHash(HashArray[0], HashArray[1], 0))
            {
                return getDocName(HashArray[1]);
            }
            else
            {
                return null;
            }
        }

        //private string getDocName(string username)
        //{
        //    string name = "";
        //    try
        //    {
        //        SqlOpenConnection();
        //        SqlCommand comm = SqlCommandConnection();
        //        comm.CommandText = "select firstName from doctor where userAccount = @userAccount";
        //        comm.Parameters.AddWithValue("@userAccount", username);

        //        SqlDataReader dr = comm.ExecuteReader();
        //        while (dr.Read())
        //        {
        //            name = dr["firstName"].ToString();
        //        }
        //        dr.Close();
        //        dr.Dispose();
        //    }
        //    catch (Exception ex)
        //    {

        //    }
        //    finally
        //    {
        //        SqlCloseConnection();
        //    }
        //    return name;
        //}

        private doctor getDocName(string username)
        {
            doctor d = new doctor();
            try
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "select * from doc_login where userAccount = @userAccount";
                comm.Parameters.AddWithValue("@userAccount", username);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    d.Uid = Convert.ToInt16((dr["uid"]));
                    d.FirstName = dr["firstName"].ToString();
                    d.LastName = dr["lastName"].ToString();
                    d.OfficeFax = dr["officeFax"].ToString();
                    d.OfficeTel = dr["officeTel"].ToString();
                    d.ImageByte = retrieveImage(dr["faceImgPath"].ToString());
                }
                dr.Close();
                dr.Dispose();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                SqlCloseConnection();
            }
            return d;
        }
        public bool DocHash(string HashStr, string UserName, int minutes)
        {
            string Pwd, ToHash;
            string sResult, sResultT, sResultToken;
            try
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "select password from doc_login where userAccount = @userAccount";
                comm.Parameters.AddWithValue("@userAccount", UserName);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Pwd = (string)(dr["password"]);

                    DateTime dt = DateTime.Now;
                    System.TimeSpan minute = new System.TimeSpan(0, 0, minutes, 0, 0);
                    dt = dt - minute;
                    //before hashing we have:
                    //USERNAME|PassWord|YYYYMMDD|HHMM
                    ToHash = UserName + "|" + Pwd + "|" + dt.ToString("yyyyMMdd") + "|" + dt.ToString("HHmm");
                    sResult = Hash(ToHash);
                    //TokenWeGotBefore
                    ToHash = dt.ToString("yyyyMMdd") + "|" + dt.ToString("HHmm");
                    sResultToken = Hash(ToHash);
                    //USERNAME|PassWord|TokenWeGotBefore
                    ToHash = UserName + "|" + Pwd + "|" + sResultToken;
                    sResultT = Hash(ToHash);

                    if ((sResult == HashStr) || (sResultT == HashStr))
                    {
                        return true;
                    }
                    else
                    {
                        if (minutes <= maxmin) // allowed maxmin+1 minutes minus 1 second to call web service
                        {
                            return DocHash(HashStr, UserName, minutes + 1);
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
                dr.Close();
                dr.Dispose();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                SqlCloseConnection();
            } return false;
        }

        //public bool DocHash(string HashStr, string UserName, int minutes)
        //{
        //    string Pwd, ToHash;
        //    string sResult, sResultT, sResultToken;
        //    //try
        //    //{
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    comm.CommandText = "select password from doctor where userAccount = @userAccount";
        //    comm.Parameters.AddWithValue("@userAccount", UserName);

        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        Pwd = (string)(dr["password"]);

        //        DateTime dt = DateTime.Now;
        //        System.TimeSpan minute = new System.TimeSpan(0, 0, minutes, 0, 0);
        //        dt = dt - minute;
        //        //before hashing we have:
        //        //USERNAME|PassWord|YYYYMMDD|HHMM
        //        ToHash = UserName + "|" + Pwd + "|" + dt.ToString("yyyyMMdd") + "|" + dt.ToString("HHmm");
        //        sResult = Hash(ToHash);
        //        //TokenWeGotBefore
        //        ToHash = dt.ToString("yyyyMMdd") + "|" + dt.ToString("HHmm");
        //        sResultToken = Hash(ToHash);
        //        //USERNAME|PassWord|TokenWeGotBefore
        //        ToHash = UserName + "|" + Pwd + "|" + sResultToken;
        //        sResultT = Hash(ToHash);

        //        if ((sResult == HashStr) || (sResultT == HashStr))
        //        {
        //            return true;
        //        }
        //        else
        //        {
        //            if (minutes <= maxmin) // allowed maxmin+1 minutes minus 1 second to call web service
        //            {
        //                return DocHash(HashStr, UserName, minutes + 1);
        //            }
        //            else
        //            {
        //                return false;
        //            }
        //        }
        //    }
        //    dr.Close();
        //    dr.Dispose();
        //    //}
        //    //catch (Exception ex)
        //    //{

        //    //}
        //    //finally
        //    //{
        //    SqlCloseConnection();
        //    //}
        //    return false;
        //}
        #endregion
        #region Patient

        public int PatientUseService(string Key)
        {
            string[] HashArray;

            // Key string: HASH|User|OptionalData
            HashArray = Key.Split('|');

            if (PatientHash(HashArray[0], HashArray[1], 0))
            {
                return getPatientUID(HashArray[1]);
            }
            else
            {
                return 0;
            }
        }

        public int getPatientUID(string username)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            //try
            //{
            int uid = 0;
            comm.CommandText = "SELECT uid FROM patient where nric = @nric";
            comm.Parameters.AddWithValue("@nric", username);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                uid = Convert.ToInt16(dr["uid"].ToString());
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return uid;
        }

        public bool PatientHash(string HashStr, string UserName, int minutes)
        {
            string Pwd, ToHash;
            string sResult, sResultT, sResultToken;
            try
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "select password from patient where nric = @nric";
                comm.Parameters.AddWithValue("@nric", UserName);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Pwd = (string)(dr["password"]);

                    DateTime dt = DateTime.Now;
                    System.TimeSpan minute = new System.TimeSpan(0, 0, minutes, 0, 0);
                    dt = dt - minute;
                    //before hashing we have:
                    //USERNAME|PassWord|YYYYMMDD|HHMM
                    ToHash = UserName + "|" + Pwd + "|" + dt.ToString("yyyyMMdd") + "|" + dt.ToString("HHmm");
                    sResult = Hash(ToHash);
                    //TokenWeGotBefore
                    ToHash = dt.ToString("yyyyMMdd") + "|" + dt.ToString("HHmm");
                    sResultToken = Hash(ToHash);
                    //USERNAME|PassWord|TokenWeGotBefore
                    ToHash = UserName + "|" + Pwd + "|" + sResultToken;
                    sResultT = Hash(ToHash);

                    if ((sResult == HashStr) || (sResultT == HashStr))
                    {
                        return true;
                    }
                    else
                    {
                        if (minutes <= maxmin) // allowed maxmin+1 minutes minus 1 second to call web service
                        {
                            return PatientHash(HashStr, UserName, minutes + 1);
                        }
                        else
                        {
                            return false;
                        }
                    }
                }
                dr.Close();
                dr.Dispose();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                SqlCloseConnection();
            }
            return false;
        }
        #endregion


        public string Hash(string ToHash)
        {
            // First we need to convert the string into bytes, which means using a text encoder.
            Encoder enc = System.Text.Encoding.ASCII.GetEncoder();

            // Create a buffer large enough to hold the string
            byte[] data = new byte[ToHash.Length];
            enc.GetBytes(ToHash.ToCharArray(), 0, ToHash.Length, data, 0, true);

            SHA1 sha1 = new SHA1CryptoServiceProvider();
            byte[] result = sha1.ComputeHash(data);

            return BitConverter.ToString(result).Replace("-", "");
        }
        #endregion

        #region CRUD for patient
        /// <summary>
        /// Generate random Password for patient
        /// </summary>
        /// <returns></returns>
        private static string CreateRandomPassword()
        {
            string allowedChars = "abcdefghijkmnopqrstuvwxyzABCDEFGHJKLMNOPQRSTUVWXYZ0123456789";
            char[] chars = new char[8];
            Random rd = new Random();

            for (int i = 0; i < 8; i++)
            {
                chars[i] = allowedChars[rd.Next(0, allowedChars.Length)];
            }
            return new string(chars);
        }

        /// <summary>
        /// Add new Patient
        /// </summary>
        /// <param name="lastName"></param>
        /// <param name="firstName"></param>
        /// <param name="title"></param>
        /// <param name="dOB"></param>
        /// <param name="gender"></param>
        /// <param name="country"></param>
        /// <param name="address1"></param>
        /// <param name="address2"></param>
        /// <param name="postalCode"></param>
        /// <param name="homePhone"></param>
        /// <param name="mobile"></param>
        /// <param name="email"></param>
        /// <param name="doctor"></param>
        /// <param name="fileName"></param>
        /// <param name="fileBtyes"></param>
        /// <returns>Patient ID</returns>
        public int addNewPatient(string lastName, string firstName, DateTime dOB, string gender, string address1, string postalCode, string homePhone, string mobile, string email, int doctor, string imgPath, string nric, DateTime adminDate)
        {
            int id = 0;
            try
            {
                string password = CreateRandomPassword();
                sendEmail(lastName, email, password);
                //string x = System.IO.Path.GetRandomFileName();
                //x = x.Replace(".", "");
                //string imgPath = uploadFaceImage(x, fileBtyes);
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "INSERT INTO patient (LastName, FirstName, dateOfBirth, gender, address1, postcode, contactNumHome, contactNumMobile, email, doctorID, faceImgPath, nric, password, adminDate) Values (@lastName, @firstName, @dOB, @gender, @address1, @postalCode, @homePhone, @mobile, @email, @doctor, @imgPath, @nric, @password, @adminDate);" + "Select Scope_Identity()";
                comm.Parameters.AddWithValue("@lastName", lastName);
                comm.Parameters.AddWithValue("@firstName", firstName);
                comm.Parameters.AddWithValue("@dOB", dOB);
                comm.Parameters.AddWithValue("@gender", gender);
                comm.Parameters.AddWithValue("@address1", address1);
                comm.Parameters.AddWithValue("@postalCode", postalCode);
                comm.Parameters.AddWithValue("@homePhone", homePhone);
                comm.Parameters.AddWithValue("@mobile", mobile);
                comm.Parameters.AddWithValue("@email", email);
                comm.Parameters.AddWithValue("@doctor", doctor);
                comm.Parameters.AddWithValue("@imgPath", imgPath);
                comm.Parameters.AddWithValue("@nric", nric);
                comm.Parameters.AddWithValue("@password", Hash(password));//password will be sent to patient email
                comm.Parameters.AddWithValue("@adminDate", adminDate);

                id = Convert.ToInt32(comm.ExecuteScalar());
            }
            catch (Exception ex)
            {
            }
            finally
            {
                SqlCloseConnection();
            }
            
            return id;// == 0 ? false : true;
        }

        /// <summary>
        /// Send patient password to patient's email (Required fixing)
        /// </summary>
        /// <param name="lastName"></param>
        /// <param name="email"></param>
        /// <param name="password"></param>
        public void sendEmail(string lastName, string email, string password)
        {
            SmtpClient MySMTPClient;
            MailMessage myEmail;

            MySMTPClient = new SmtpClient("smtp.gmail.com", 587);
            MySMTPClient.Credentials = new NetworkCredential("g4ewdt@gmail.com", "inventory");
            MySMTPClient.EnableSsl = true;
            myEmail = new MailMessage(new MailAddress("g4ewdt@gmail.com"), new MailAddress(email));
            myEmail.Body = "<table><tr><td>" + "Dear " + lastName + ", <br>The following is your password to TARA. <br> Your password: " + password + "<br> Do note that passwords are case sensitive, enter them as what is given to you. If you copy and paste your password, make sure you did not copy extra space in front or after the password. <br> Please change your password immediately after login. Keep your new password safely for your future logins. <br>Should you encounter any problem with regards to your account, please contact our helpdesk support at g4ewdt@gmail.com, stating clearly your problem. <br> Thank you. <br> Account Support Divison</table></tr></td>";
            myEmail.Subject = "Tara Details";
            myEmail.IsBodyHtml = true;
            try
            {
                MySMTPClient.Send(myEmail);
            }
            catch (Exception ex)
            {
                // Display Exception Details
            }
            //string body = "Your new password is " + password;
            //MailMessage MyMailMessage = new MailMessage();
            //MyMailMessage.From = new MailAddress("g4ewdt@gmail.com");
            //MyMailMessage.To.Add(email.Trim());

            //MyMailMessage.Subject = "New Password";
            //MyMailMessage.IsBodyHtml = true;
            //MyMailMessage.Body = "<table><tr><td>" + "Dear " + lastName + ", <br>The following is your password to TARA. <br> Your password: " + password + "<br> Do note that passwords are case sensitive, enter them as what is given to you. If you copy and paste your password, make sure you did not copy extra space in front or after the password. <br> Please change your password immediately after login. Keep your new password safely for your future logins. <br>Should you encounter any problem with regards to your account, please contact our helpdesk support at g4ewdt@gmail.com, stating clearly your problem. <br> Thank you. <br> Account Support Divison</table></tr></td>";

            //SmtpClient SMTPServer = new SmtpClient("smtp.gmail.com");
            //SMTPServer.Port = 587;
            //SMTPServer.Credentials = new System.Net.NetworkCredential("g4ewdt@gmail.com", "inventory"); //need a password
            //SMTPServer.EnableSsl = true;
        }

        /// <summary>
        /// Update patient profile
        /// </summary>
        /// <param name="lastName"></param>
        /// <param name="firstName"></param>
        /// <param name="dOB"></param>
        /// <param name="gender"></param>
        /// <param name="country"></param>
        /// <param name="address1"></param>
        /// <param name="postalCode"></param>
        /// <param name="homePhone"></param>
        /// <param name="mobile"></param>
        /// <param name="email"></param>
        /// <param name="doctor"></param>
        /// <param name="fileName"></param>
        /// <param name="fileBtyes"></param>
        /// <param name="nric"></param>
        /// <returns></returns>
        public bool updatePatient(int uid, string lastName, string firstName, DateTime dOB, string gender, string address1, string postalCode, string homePhone, string mobile, string email, string imgPath, string nric, DateTime adminDate)
        {
            int id = 0;
            if (imgPath != null)
            {
                //string imgPath = uploadFaceImage(fileName, fileBtyes);
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "Update patient set LastName =@lastName, FirstName =@firstName, dateOfBirth=@dOB, gender=@gender, address1=@address1, postcode=@postalCode, contactNumHome=@homePhone, contactNumMobile=@mobile, email=@email, faceImgPath=@imgPath, nric=@nric, adminDate=@adminDate where uid=@patientID";
                comm.Parameters.AddWithValue("@lastName", lastName);
                comm.Parameters.AddWithValue("@firstName", firstName);
                comm.Parameters.AddWithValue("@dOB", dOB);
                comm.Parameters.AddWithValue("@gender", gender);
                comm.Parameters.AddWithValue("@address1", address1);
                comm.Parameters.AddWithValue("@postalCode", postalCode);
                comm.Parameters.AddWithValue("@homePhone", homePhone);
                comm.Parameters.AddWithValue("@mobile", mobile);
                comm.Parameters.AddWithValue("@email", email);
                comm.Parameters.AddWithValue("@imgPath", imgPath);
                comm.Parameters.AddWithValue("@nric", nric);
                comm.Parameters.AddWithValue("@adminDate", adminDate);
                comm.Parameters.AddWithValue("@patientID", uid);

                id = Convert.ToInt32(comm.ExecuteNonQuery());
                SqlCloseConnection();
            }
            else
            {   
                //Photo not required to be updated
                //string imgPath = uploadFaceImage(fileName, fileBtyes);
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "Update patient set LastName =@lastName, FirstName =@firstName, dateOfBirth=@dOB, gender=@gender, address1=@address1, postcode=@postalCode, contactNumHome=@homePhone, contactNumMobile=@mobile, email=@email, nric=@nric, adminDate=@adminDate where uid=@patientID";
                comm.Parameters.AddWithValue("@lastName", lastName);
                comm.Parameters.AddWithValue("@firstName", firstName);
                comm.Parameters.AddWithValue("@dOB", dOB);
                comm.Parameters.AddWithValue("@gender", gender);
                comm.Parameters.AddWithValue("@address1", address1);
                comm.Parameters.AddWithValue("@postalCode", postalCode);
                comm.Parameters.AddWithValue("@homePhone", homePhone);
                comm.Parameters.AddWithValue("@mobile", mobile);
                comm.Parameters.AddWithValue("@email", email);
                //comm.Parameters.AddWithValue("@imgPath", imgPath);
                comm.Parameters.AddWithValue("@nric", nric);
                comm.Parameters.AddWithValue("@adminDate", adminDate);
                comm.Parameters.AddWithValue("@patientID", uid);

                id = Convert.ToInt32(comm.ExecuteNonQuery());
                SqlCloseConnection();
            }
            return id == 0 ? false : true;
        }

        /// <summary>
        /// Retrieve all patients that the doctor is in-charge of.
        /// </summary>
        /// <param name="docname"></param>
        /// <returns></returns>
        public List<patient> retrieveAllOwnPatients(int docID)
        {
            List<patient> result = new List<patient>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            try
            {
                comm.CommandText = "SELECT * FROM patient where doctorID=@doc";
                comm.Parameters.AddWithValue("@doc", docID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    patient p = new patient();
                    p.Address1 = dr["Address1"].ToString();
                    p.Doc = Convert.ToInt32(dr["doctorID"].ToString());
                    p.FirstName = dr["FirstName"].ToString();
                    p.LastName = dr["LastName"].ToString();
                    p.Postcode = dr["Postcode"].ToString();
                    p.Uid = Convert.ToInt16(dr["uid"].ToString());
                    try
                    {
                        p.DateOfBirth = Convert.ToDateTime(dr["dateOfBirth"].ToString());
                    }
                    catch (Exception) { }
                    p.Email = dr["email"].ToString();
                    p.Gender = dr["gender"].ToString();
                    p.ContactNumHome = dr["contactNumHome"].ToString();
                    p.ContactNumMob = dr["contactNumMobile"].ToString();
                    p.Country = dr["country"].ToString();
                    //p.ImagePath = dr["faceImgPath"].ToString();
                    p.ImageByte = retrieveImage(dr["faceImgPath"].ToString());
                    p.Nric = dr["nric"].ToString();
                    p.AdminDate = Convert.ToDateTime(dr["adminDate"].ToString());
                    result.Add(p);
                }
                dr.Close();
                dr.Dispose();
                SqlCloseConnection();
                foreach (patient temp in result)
                {
                    temp.JointSeverity = getAllJoint(temp.Uid);
                }
            }
            catch (SqlException)
            {
                return null;
            }
            return result;
        }

        /// <summary>
        /// Retrieve the number of patients in-charge by this doctor.
        /// </summary>
        /// <param name="docname"></param>
        /// <returns></returns>
        public int retrieveNumofOwnPatients(string docname)
        {
            int count = 0;

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "select count(uid) as count from patient where doctor = @doc";
            comm.Parameters.AddWithValue("@doc", docname);
            //count = comm.ExecuteNonQuery();
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                count = Convert.ToInt32(dr["count"].ToString());
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();

            return count;
        }

        /// <summary>
        /// Get complete detail of a patient
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public patient retrievePatient(int uid)
        {
            patient p = new patient();

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            //try
            //{
            comm.CommandText = "SELECT * FROM patient where uid = @uid";
            comm.Parameters.AddWithValue("@uid", uid);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                p.Address1 = dr["Address1"].ToString();
                p.Doc = Convert.ToInt32(dr["doctorID"].ToString());
                p.FirstName = dr["FirstName"].ToString();
                p.LastName = dr["LastName"].ToString();
                p.Postcode = dr["Postcode"].ToString();
                p.Uid = Convert.ToInt16(dr["uid"].ToString());
                try
                {
                    p.DateOfBirth = Convert.ToDateTime(dr["dateOfBirth"].ToString());
                }
                catch (Exception) { }
                p.Email = dr["email"].ToString();
                p.Gender = dr["gender"].ToString();
                p.ContactNumHome = dr["contactNumHome"].ToString();
                p.ContactNumMob = dr["contactNumMobile"].ToString();
                p.Country = dr["country"].ToString();
                //p.ImagePath = dr["faceImgPath"].ToString();
                p.ImageByte = retrieveImage(dr["faceImgPath"].ToString());
                p.Nric = dr["nric"].ToString();
                p.AdminDate = Convert.ToDateTime(dr["adminDate"].ToString());
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            p.JointSeverity = getAllJoint(p.Uid);
            return p;
        }

        /// <summary>
        /// Search for the patient by the doctor
        /// </summary>
        /// <param name="toSearch"></param>
        /// <returns></returns>
        public List<patient> searchPatient(string toSearch, int doctorID)
        {
            List<patient> result = new List<patient>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            try
            {
                comm.CommandText = "SELECT * FROM patient where (LastName=@toSearch or FirstName=@toSearch or nric=@toSearch) and doctorID=@doctorID";
                comm.Parameters.AddWithValue("@toSearch", toSearch);
                comm.Parameters.AddWithValue("@doctorID", doctorID);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    patient p = new patient();
                    p.Address1 = dr["Address1"].ToString();
                    p.Doc = Convert.ToInt32(dr["doctorID"].ToString());
                    p.FirstName = dr["FirstName"].ToString();
                    p.LastName = dr["LastName"].ToString();
                    p.Postcode = dr["Postcode"].ToString();
                    p.Uid = Convert.ToInt16(dr["uid"].ToString());
                    try
                    {
                        p.DateOfBirth = Convert.ToDateTime(dr["dateOfBirth"].ToString());
                    }
                    catch (Exception) { }
                    p.Email = dr["email"].ToString();
                    p.Gender = dr["gender"].ToString();
                    p.ContactNumHome = dr["contactNumHome"].ToString();
                    p.ContactNumMob = dr["contactNumMobile"].ToString();
                    p.Country = dr["country"].ToString();
                    //p.ImagePath = dr["faceImgPath"].ToString();
                    p.ImageByte = retrieveImage(dr["faceImgPath"].ToString());
                    p.Nric = dr["nric"].ToString();
                    p.AdminDate = Convert.ToDateTime(dr["adminDate"].ToString());
                    result.Add(p);
                }
                dr.Close();
                dr.Dispose();
                SqlCloseConnection();
                foreach (patient temp in result)
                {
                    temp.JointSeverity = getAllJoint(temp.Uid);
                }
            }
            catch (SqlException)
            {
                return null;
            }
            return result;
        }

        #endregion

        #region patientImg
        /// <summary>
        /// Upload Image into the Blob
        /// </summary>
        /// <param name="fileName">FileUpload1.FileName</param>
        /// <param name="fileBytes">FileUpload1.FileBytes</param>
        /// <returns>container/filename</returns>
        public string uploadFaceImage(string fileName, byte[] fileBytes)
        {
            bool result = false;
            try
            {
                CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>
                {
                    configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));
                });
                // Get the storage account reference
                CloudStorageAccount objStorage = CloudStorageAccount.FromConfigurationSetting("BlobConnectionString");
                // get the Client reference using storage blobend point
                CloudBlobClient objClient = new CloudBlobClient(objStorage.BlobEndpoint, objStorage.Credentials);
                // Get Container reference
                // If i set to face, i can't retrieve it T>T
                //CloudBlobContainer objContainer = objClient.GetContainerReference("face");
                CloudBlobContainer objContainer = objClient.GetContainerReference("tarastorage");
                // Get blob reference
                CloudBlob obj = objContainer.GetBlobReference(fileName);
                // Set meta values
                obj.Metadata["MetaName"] = "meta";
                // Open a stream using the cloud object
                BlobStream blobstream = obj.OpenWrite();
                // Write the stream to the blob database
                blobstream.Write(fileBytes, 0, fileBytes.Count());
                blobstream.Close();
                result = true;
            }
            catch (Exception)
            {
                result = false;
            }
            return result == true ? "http://tarastorage.blob.core.windows.net/tarastorage/" + fileName : String.Empty;
        }

        public string updateFaceImage(string fileName, byte[] fileBytes)
        {
            bool result = false;
            try
            {
                CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>
                {
                    configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));
                });
                // Get the storage account reference
                CloudStorageAccount objStorage = CloudStorageAccount.FromConfigurationSetting("BlobConnectionString");
                // get the Client reference using storage blobend point
                CloudBlobClient objClient = new CloudBlobClient(objStorage.BlobEndpoint, objStorage.Credentials);
                // Get Container reference
                // If i set to face, i can't retrieve it T>T
                //CloudBlobContainer objContainer = objClient.GetContainerReference("face");
                CloudBlobContainer objContainer = objClient.GetContainerReference("tarastorage");
                // Get blob reference
                CloudBlob obj = objContainer.GetBlobReference(fileName);
                // Set meta values
                obj.Metadata["MetaName"] = "meta";
                // Open a stream using the cloud object
                BlobStream blobstream = obj.OpenWrite();
                // Write the stream to the blob database
                blobstream.Write(fileBytes, 0, fileBytes.Count());
                blobstream.Close();
                result = true;

            }
            catch (Exception)
            {
                result = false;
            }
            return result == true ? "http://tarastorage.blob.core.windows.net/tarastorage/" + fileName : String.Empty;
        }
        /// <summary>
        /// retrieve search the Image in mycontainer
        /// </summary>
        /// <param name="txtSearch">Name of Image Example asd.jpg</param>
        /// <returns>System.Drawing.Image</returns>
        public byte[] searchAndretrieveImage(string txtSearch)
        {
            CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>
            {
                configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));
            });
            // Get the storage account reference
            CloudStorageAccount objStorage = CloudStorageAccount.FromConfigurationSetting("BlobConnectionString");
            // get the Client reference using storage blobend point
            CloudBlobClient objClient = new CloudBlobClient(objStorage.BlobEndpoint, objStorage.Credentials);
            // Get Container reference
            CloudBlobContainer objContainer = objClient.GetContainerReference("mycontainer");

            // Get the blob reference using the blob name provided in the search
            CloudBlob obj = objContainer.GetBlobReference(txtSearch);
            BlobStream blobstream = obj.OpenRead();

            // Create the image object and display the same on the browser response
            System.Drawing.Image objimg = null;

            try
            {
                objimg = System.Drawing.Image.FromStream(blobstream, true);

                //convert from Drawing.Image to byte[]
                MemoryStream ms = new MemoryStream();
                objimg.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
                byte[] asd = ms.ToArray();


                //// create a bitmapimage from byte[] 
                //// example only
                //BitmapImage bi = new BitmapImage();
                //bi.BeginInit();
                //bi.StreamSource = new MemoryStream(asd);
                //bi.EndInit();


                return asd;
            }
            catch (Exception)
            {
                return null;
            }

        }

        /// <summary>
        /// retrieve Image base on URL
        /// </summary>
        /// <param name="txtSearch">Name of Image Example asd.jpg</param>
        /// <returns>System.Drawing.Image</returns>
        public byte[] retrieveImage(string URL)
        {
            string txtSearch = "";
            string container = "";
            //char[] delimiters = new char[] { '\r', '\n' };
            string[] parts = URL.Split(new char[] { '/' }, StringSplitOptions.RemoveEmptyEntries);
            for (int i = 0; i < parts.Length; i++)
            {
                if (parts[i] == "face")
                {
                    container = "face";
                }
                else if (parts[i] == "tarastorage")
                {
                    container = "tarastorage";
                }
                else if (parts[i] != "http:" && parts[i] != "tarastorage.blob.core.windows.net")
                {
                    txtSearch = parts[i];
                }
            }

            CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>
            {
                configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));
            });
            // Get the storage account reference
            CloudStorageAccount objStorage = CloudStorageAccount.FromConfigurationSetting("BlobConnectionString");
            // get the Client reference using storage blobend point
            CloudBlobClient objClient = new CloudBlobClient(objStorage.BlobEndpoint, objStorage.Credentials);
            // Get Container reference
            CloudBlobContainer objContainer = objClient.GetContainerReference("tarastorage");

            // Get the blob reference using the blob name provided in the search
            CloudBlob obj = objContainer.GetBlobReference(txtSearch);//);
            BlobStream blobstream = obj.OpenRead();

            // Create the image object and display the same on the browser response
            //System.Drawing.Image objimg = null;

            try
            {
                //objimg = System.Drawing.Image.FromStream(blobstream, true);

                //convert from Drawing.Image to byte[]
                MemoryStream ms = new MemoryStream();
                obj.DownloadToStream(ms);
                byte[] asd = ms.ToArray();

                //// create a bitmapimage from byte[] 
                //// example only
                //BitmapImage bi = new BitmapImage();
                //bi.BeginInit();
                //bi.StreamSource = new MemoryStream(asd);
                //bi.EndInit();

                return asd;
            }
            catch (Exception)
            {
                return null;
            }
        }
        #endregion

        #region CRUD for patient_exe

        /// <summary>
        /// NOT IN USE
        /// </summary>
        /// <param name="exe"></param>
        /// <param name="patient"></param>
        /// <param name="dateassigned"></param>
        /// <returns></returns>
        public int retrieveExeIDAssignedtoPatient(int exe, int patient, DateTime dateassigned)
        {
            int result = 0;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT uid FROM patient_exe WHERE exeID = @exe AND patientID = @patient AND dateassigned = @dateassigned";
            comm.Parameters.AddWithValue("@exe", exe);
            comm.Parameters.AddWithValue("@patient", patient);
            comm.Parameters.AddWithValue("@dateassigned", dateassigned);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = Convert.ToInt16(dr["uid"].ToString());
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            return result;
        }

        /// <summary>
        /// Retrieve exe assigned to patient
        /// </summary>
        /// <param name="patientID"></param>
        /// <returns></returns>
        public List<EXE> retrieveGameAssignedtoPatient(int patientID)
        {
            List<EXE> result = new List<EXE>();
            List<EXE> result1 = new List<EXE>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT patient_exe.uid, exe.path, exe.description, exe.uid AS exeID, exe.name, exe.iconPath, exe.version, exe.developer, exe.targetedMotion FROM patient_exe, exe WHERE patient_exe.patientID = @patientID AND patient_exe.exeID = exe.uid and exe.exeType = 1 and patient_exe.assigned = 'assigned' Order BY dateassigned DESC";
            comm.Parameters.AddWithValue("@patientID", patientID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                EXE temp = new EXE();
                temp.Description = dr["description"].ToString();
                temp.Path = dr["path"].ToString();
                temp.Uid = Convert.ToInt16(dr["exeID"].ToString());
                temp.Name = dr["name"].ToString();
                try
                {
                    temp.Patient_exe_id = Convert.ToInt16(dr["uid"].ToString());
                }
                catch (Exception) { }
                temp.ImageByte = retrieveImage(dr["iconPath"].ToString());
                temp.Version = dr["version"].ToString();
                temp.Developer = dr["developer"].ToString();
                temp.TargetedMotion = dr["targetedMotion"].ToString();
                result.Add(temp);
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            foreach (EXE temp in result)
            {
                temp.Tag = retrieveTag(temp.Uid);
                temp.CustKey = retrieve_exe_custKey(temp.Uid);
                temp.SettingsList = retrieve_patient_exe_custKey(temp.Patient_exe_id);
                result1.Add(temp);
            }
            return result1;
        }

        /// <summary>
        /// Unassign a EXE from a patient
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public bool deleteExeAssignedtoPatient(int uid)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "DELETE FROM patient_exe WHERE patientID = @uid";
            comm.Parameters.AddWithValue("@uid", uid);
            int count = comm.ExecuteNonQuery();
            SqlCloseConnection();
            return count == 0 ? false : true;
        }

        /// <summary>
        /// Assign EXE to patient
        /// </summary>
        /// <param name="patientID"></param>
        /// <param name="exeList"></param>
        /// <returns></returns>
        public bool createExeAssignedtoPatient(int patientID, List<EXE> exeList)
        {
            DateTime dateofplay = DateTime.Now;
            int count = 0;
            foreach (EXE exe in exeList)
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "INSERT INTO patient_exe(exeID, patientID, dateassigned) VALUES(@exeID, @patientID, @dateofplay)";
                comm.Parameters.AddWithValue("@exeID", exe.Uid);
                comm.Parameters.AddWithValue("@patientID", patientID);
                comm.Parameters.AddWithValue("@dateofplay", dateofplay);
                count = comm.ExecuteNonQuery();
                SqlCloseConnection();

                int patient_exe_id = retrieveExeIDAssignedtoPatient(exe.Uid, patientID, dateofplay);
                createPatientExeCustkey(exe.SettingsList, patient_exe_id);
            }
            return count == 0 ? false : true;
        }

        public int assignedGametoPatient(int patientID, int exeID)
        {
            DateTime dateofplay = DateTime.Now;

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO patient_exe(exeID, patientID, dateassigned, assigned) VALUES(@exeID, @patientID, @dateassigned, 'assigned');" + "Select Scope_Identity()";
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@dateassigned", dateofplay);
            int id = Convert.ToInt32(comm.ExecuteScalar());
            SqlCloseConnection();

            return id;// == 0 ? false : true;
        }

        public bool unassignedGameToPatient(int patientID, int exeID)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Update patient_exe set assigned=@assigned where patientID = @patientID and exeID = @exeID;";
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@assigned", "unassigned");
            int result = comm.ExecuteNonQuery();
            SqlCloseConnection();
            return result == 0 ? false : true;
        }

        # endregion

        #region retrieve_patient_exe_custKey

        /// <summary>
        /// Customize the setting for the exe assigned to the patient.
        /// </summary>
        /// <param name="settingsList"></param>
        /// <param name="patient_exe_id"></param>
        /// <returns></returns>
        public bool createPatientExeCustkey(List<KeyValuePair<string, string>> settingsList, int patient_exe_id)
        {
            int count = 0;
            foreach (KeyValuePair<string, string> temp in settingsList)
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "INSERT INTO patient_exe_custKey VALUES(@patient_exe_id, @custKey, @custValue)";
                comm.Parameters.AddWithValue("@patient_exe_id", patient_exe_id);
                comm.Parameters.AddWithValue("@custKey", temp.Key.ToString());
                comm.Parameters.AddWithValue("@custValue", temp.Value.ToString());
                count = Convert.ToInt16(comm.ExecuteNonQuery());
                SqlCloseConnection();
            }

            return count == 0 ? false : true;
        }

        /// <summary>
        /// Retrieve game setting for patient (API for Game Developers)
        /// </summary>
        /// <param name="patient_exe_id"></param>
        /// <returns></returns>
        public List<KeyValuePair<string, string>> retrieve_patient_exe_custKey(int patient_exe_id)
        {
            List<KeyValuePair<string, string>> result = new List<KeyValuePair<string, string>>();

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT custKey, custValue FROM patient_exe_custKey WHERE patient_exe_id = @patient_exe_id";
            comm.Parameters.AddWithValue("@patient_exe_id", patient_exe_id);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                KeyValuePair<string, string> temp = new KeyValuePair<string, string>(dr["custKey"].ToString(), dr["custValue"].ToString());
                result.Add(temp);
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();

            return result;
        }

        #endregion

        # region CRUD for exe
        /// <summary>
        /// Add a new EXE (API for Game Developers.)
        /// </summary>
        /// <param name="name"></param>
        /// <param name="path"></param>
        /// <param name="desc"></param>
        /// <param name="exeType"></param>
        /// <param name="custKey"></param>
        /// <returns></returns>
        public bool createEXE(string name, string path, string desc, int exeType, List<string> custKey)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO exe Values (@name, @path, @desc,@exeType)";
            comm.Parameters.AddWithValue("@name", name);
            comm.Parameters.AddWithValue("@path", path);
            comm.Parameters.AddWithValue("@desc", desc);
            comm.Parameters.AddWithValue("@exeType", exeType);
            int result = comm.ExecuteNonQuery();
            SqlCloseConnection();

            // create_exe_custKey

            return result == 0 ? false : true;
        }

        public List<EXE> retrieveAllEXE()
        {
            List<EXE> result = new List<EXE>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT exe.uid, exe.name, exe.path, exe.description, exe.version, exe.developer, exe.targetedMotion FROM exe, exeType WHERE exe.exeType = exeType.uid";
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                EXE p = new EXE();
                p.Description = dr["description"].ToString();
                p.Name = dr["name"].ToString();
                p.Path = dr["path"].ToString();
                p.Description = dr["description"].ToString();
                p.Developer = dr["developer"].ToString();
                p.Version = dr["version"].ToString();
                p.TargetedMotion = dr["targetedMotion"].ToString();
                p.Uid = Convert.ToInt16(dr["uid"].ToString());

                result.Add(p);
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            if (result.Count != 0)
            {
                foreach (EXE exe in result)
                {
                    exe.Tag = retrieveTag(exe.Uid);
                    exe.ExeStage = retrieveExeStage(exe.Uid);
                    exe.CustKey = retrieve_exe_custKey(exe.Uid);
                }
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<EXE> retrieveAllTreatment()
        {
            List<EXE> result = new List<EXE>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT exe.uid, exe.name, exe.path, exe.description, exe.version, exe.developer, exe.targetedMotion, exe.iconPath FROM exe, exeType WHERE exe.exeType = exeType.uid and exeType.description = 'treatmentGame'";
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                EXE p = new EXE();
                p.Description = dr["description"].ToString();
                p.Name = dr["name"].ToString();
                p.Path = dr["path"].ToString();
                p.Description = dr["description"].ToString();
                //try
                //{
                //    p.ExeTypedescription = dr["exeTypedescription"].ToString();
                //}
                //catch (Exception) { }
                p.Developer = dr["developer"].ToString();
                p.Version = dr["version"].ToString();
                p.TargetedMotion = dr["targetedMotion"].ToString();
                p.Uid = Convert.ToInt16(dr["uid"].ToString());
                p.ImageByte = retrieveImage(dr["iconPath"].ToString());
                result.Add(p);
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            if (result.Count != 0)
            {
                foreach (EXE exe in result)
                {
                    exe.Tag = retrieveTag(exe.Uid);
                    exe.ExeStage = retrieveExeStage(exe.Uid);
                    exe.CustKey = retrieve_exe_custKey(exe.Uid);
                }
            }
            return result;
        }

        public List<EXE> retrieveAllAssessment()
        {
            List<EXE> result = new List<EXE>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT exe.uid, exe.name, exe.path, exe.description, exe.version, exe.developer, exe.targetedMotion FROM exe, exeType WHERE exe.exeType = exeType.uid and exeType.description = 'angleMeasurement'";
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                EXE p = new EXE();
                p.Description = dr["description"].ToString();
                p.Name = dr["name"].ToString();
                p.Path = dr["path"].ToString();
                p.Description = dr["description"].ToString();
                p.Developer = dr["developer"].ToString();
                p.Version = dr["version"].ToString();
                p.TargetedMotion = dr["targetedMotion"].ToString();
                p.Uid = Convert.ToInt16(dr["uid"].ToString());

                result.Add(p);
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            if (result.Count != 0)
            {
                foreach (EXE exe in result)
                {
                    exe.Tag = retrieveTag(exe.Uid);
                    exe.ExeStage = retrieveExeStage(exe.Uid);
                    exe.CustKey = retrieve_exe_custKey(exe.Uid);
                }
            }
            return result;
        }

        /// <summary>
        /// Retrieve EXE of a given type.
        /// </summary>
        /// <param name="exeType"></param>
        /// <returns></returns>
        public List<EXE> retrieveAllEXEofExeType(int exeType)
        {
            List<EXE> result = new List<EXE>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM  exe";
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                if (Convert.ToInt16(dr["exeType"].ToString()) == exeType)
                {
                    EXE p = new EXE();
                    p.Description = dr["description"].ToString();
                    p.Name = dr["name"].ToString();
                    p.Path = dr["path"].ToString();
                    p.Uid = Convert.ToInt16(dr["uid"].ToString());
                    try
                    {
                        p.ExeType = Convert.ToInt16(dr["exeType"].ToString());
                        p.ExeTypedescription = dr["exeTypedescription"].ToString();
                    }
                    catch (Exception) { }

                    result.Add(p);
                }
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            if (result.Count != 0)
            {
                foreach (EXE exe in result)
                {
                    exe.Tag = retrieveTag(exe.Uid);
                    exe.ExeStage = retrieveExeStage(exe.Uid);
                    exe.CustKey = retrieve_exe_custKey(exe.Uid);
                }
            }
            return result;
        }

        /// <summary>
        /// Retrieve EXE of a given type with respect to the joint.
        /// </summary>
        /// <param name="exeType"></param>
        /// <param name="joint"></param>
        /// <returns></returns>
        public List<EXE> retrieveAllExeOfExeType(int exeType, int joint)
        {
            List<EXE> result = retrieveAllEXEofExeType(exeType);
            foreach (EXE exe in result)
            {
                if (Convert.ToInt16(exe.Tag) != joint)
                {
                    result.Remove(exe);
                }
            }
            return result;
        }

        /// <summary>
        /// Retrieve the property of a given EXE
        /// </summary>
        /// <param name="exeID"></param>
        /// <returns></returns>
        public EXE retrieveEXE(int exeID)
        {
            EXE result = new EXE();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM exe WHERE uid = @exeID";
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result.Description = dr["description"].ToString();
                result.Name = dr["name"].ToString();
                result.Path = dr["path"].ToString();
                result.Uid = Convert.ToInt16(dr["uid"].ToString());
                try
                {
                    result.ExeType = Convert.ToInt16(dr["exeType"].ToString());
                    result.ExeTypedescription = dr["exeTypedescription"].ToString();
                }
                catch (Exception) { }
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            result.Tag = retrieveTag(result.Uid);
            result.ExeStage = retrieveExeStage(result.Uid);
            result.CustKey = retrieve_exe_custKey(result.Uid);
            return result;
        }

        /// <summary>
        /// Retrieve the list of joint and tag for an EXE
        /// </summary>
        /// <param name="exeID"></param>
        /// <returns></returns>
        public List<string> retrieveTag(int exeID)
        {
            List<string> result = new List<string>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT jointID FROM exe_tag WHERE exeID = @exeID";
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                string temp = dr["jointID"].ToString();
                result.Add(temp);
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            return result;
        }

        /// <summary>
        /// Retrieve all exe id that belongs to that tag
        /// </summary>
        /// <param name="joint"></param>
        /// <returns></returns>
        public List<int> retrieveExeIDofTag(int joint)
        {
            List<int> result = new List<int>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT exeID FROM exe_tag WHERE joint = @joint";
            comm.Parameters.AddWithValue("@joint", joint);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                int temp = Convert.ToInt16(dr["exeID"].ToString());
                result.Add(temp);
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            return result;
        }

        /// <summary>
        /// Retrieve all exe id that belongs to that tag
        /// </summary>
        /// <param name="joint"></param>
        /// <returns></returns>
        public List<EXE> retrieveExeofTag(int joint)
        {
            List<int> listExeID = new List<int>();
            listExeID = this.retrieveExeIDofTag(joint);
            List<EXE> result = new List<EXE>();
            foreach (int i in listExeID)
            {
                result.Add(this.retrieveEXE(i));
            }
            return result;
        }

        /// <summary>
        /// Retrieve the stage details for an EXE
        /// </summary>
        /// <param name="exeID"></param>
        /// <returns></returns>
        public List<stage> retrieveExeStage(int exeID)
        {
            List<stage> result = new List<stage>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT exe_stage.stage,stage.name FROM exe_stage,stage WHERE exe_stage.exe = @exeID AND stage.uid = exe_stage.stage";
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                stage temp = new stage();
                temp.Name = dr["name"].ToString();
                temp.Uid = Convert.ToInt16(dr["stage"].ToString());
                result.Add(temp);
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            return result;
        }

        /// <summary>
        /// Retrieve the Customize Key for the given EXE
        /// </summary>
        /// <param name="exeID"></param>
        /// <returns></returns>
        public List<string> retrieve_exe_custKey(int exeID)
        {
            List<string> result = new List<string>();

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT custKey FROM exe_custKey WHERE exeID = @exeID";
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result.Add((dr["custKey"].ToString()));
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();

            return result;
        }

        public List<EXE> searchTreatment(string toSearch)
        {
            List<EXE> result = new List<EXE>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            try
            {
                comm.CommandText = "SELECT * FROM exe where exeType<>2 and name like @toSearch";
                comm.Parameters.AddWithValue("@toSearch", "%" + toSearch + "%");

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    EXE p = new EXE();
                    p.Description = dr["description"].ToString();
                    p.Name = dr["name"].ToString();
                    p.Path = dr["path"].ToString();
                    p.Description = dr["description"].ToString();
                    //try
                    //{
                    //    p.ExeTypedescription = dr["exeTypedescription"].ToString();
                    //}
                    //catch (Exception) { }
                    p.Developer = dr["developer"].ToString();
                    p.Version = dr["version"].ToString();
                    p.TargetedMotion = dr["targetedMotion"].ToString();
                    p.Uid = Convert.ToInt16(dr["uid"].ToString());

                    result.Add(p);
                }
                dr.Close();
                dr.Dispose();
                SqlCloseConnection();
            }
            catch (SqlException)
            {
                return null;
            }
            if (result.Count != 0)
            {
                foreach (EXE exe in result)
                {
                    exe.Tag = retrieveTag(exe.Uid);
                    exe.ExeStage = retrieveExeStage(exe.Uid);
                    exe.CustKey = retrieve_exe_custKey(exe.Uid);
                }
            }
            return result;
        }

        public List<EXE> searchAssessment(string toSearch)
        {
            List<EXE> result = new List<EXE>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            try
            {
                comm.CommandText = "SELECT * FROM exe where exeType<>1 and name like @toSearch";
                comm.Parameters.AddWithValue("@toSearch", "%" + toSearch + "%");

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    EXE p = new EXE();
                    p.Description = dr["description"].ToString();
                    p.Name = dr["name"].ToString();
                    p.Path = dr["path"].ToString();
                    p.Description = dr["description"].ToString();
                    //try
                    //{
                    //    p.ExeTypedescription = dr["exeTypedescription"].ToString();
                    //}
                    //catch (Exception) { }
                    p.Developer = dr["developer"].ToString();
                    p.Version = dr["version"].ToString();
                    p.TargetedMotion = dr["targetedMotion"].ToString();
                    p.Uid = Convert.ToInt16(dr["uid"].ToString());

                    result.Add(p);
                }
                dr.Close();
                dr.Dispose();
                SqlCloseConnection();
            }
            catch (SqlException)
            {
                return null;
            }
            if (result.Count != 0)
            {
                foreach (EXE exe in result)
                {
                    exe.Tag = retrieveTag(exe.Uid);
                    exe.ExeStage = retrieveExeStage(exe.Uid);
                    exe.CustKey = retrieve_exe_custKey(exe.Uid);
                }
            }
            return result;
        }
        # endregion

        # region CRUD stage
        /// <summary>
        /// Get the stage name with respect to the stageID
        /// </summary>
        /// <param name="stageID"></param>
        /// <returns></returns>
        public string retrieve_stageName(int stageID)
        {
            string result = "";

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT name FROM stage WHERE uid = @stageID";
            comm.Parameters.AddWithValue("@stageID", stageID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = ((dr["name"].ToString()));
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();

            return result;
        }

        #endregion

        # region CRUD for gamePlay
        /// <summary>
        /// Create a new game play when patient play a game (API for Game Developers)
        /// </summary>
        /// <param name="sessionID"></param>
        /// <param name="stageID"></param>
        /// <param name="joint"></param>
        /// <param name="specialInstructions"></param>
        /// <param name="gameKey"></param>
        /// <param name="gameValue"></param>
        /// <returns></returns>
        public bool createNewGamePlay(int sessionID, string joint, string gameKey, string gameValue)
        {
            DateTime dateofplay = DateTime.Now;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO gamePlay(sessionID, jointID, gameKey, gameValue) Values (@sessionID, @joint, @gameKey, @gameValue)";
            comm.Parameters.AddWithValue("@sessionID", sessionID);
            comm.Parameters.AddWithValue("@joint", Convert.ToInt16(joint));
            comm.Parameters.AddWithValue("@gameKey", gameKey);
            comm.Parameters.AddWithValue("@gameValue", gameValue);
            int result = comm.ExecuteNonQuery();
            SqlCloseConnection();
            return result == 0 ? false : true;
        }

        /// <summary>
        /// NOT IN USE
        /// </summary>
        /// <returns></returns>
        public List<gamePlay> retrieveAllGamePlay()
        {
            List<gamePlay> result = new List<gamePlay>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM gamePlay";
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                gamePlay p = new gamePlay();
                p.Uid = Convert.ToInt16(dr["uid"].ToString());
                try
                {
                    p.StageID = Convert.ToInt16(dr["stageID"].ToString());
                }
                catch (Exception) { }
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                p.SpecialInstructions = dr["specialInstructions"].ToString();
                p.JointID = Convert.ToInt16(dr["joint"].ToString());
                p.GameKey = dr["gameKey"].ToString();
                p.GameValue = dr["gameValue"].ToString();
                result.Add(p);
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return result;
        }

        /// <summary>
        /// Retrieve gameplay with sessionID
        /// </summary>
        /// <param name="sessionID"></param>
        /// <returns></returns>
        public List<gamePlay> retrieveAllGamePlayofSession(int sessionID)
        {
            List<gamePlay> result = new List<gamePlay>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT gamePlay.uid, gameplay.sessionID, gameplay.stageID, gameplay.jointID, gameplay.gameKey,gameplay.gameValue FROM gamePlay WHERE gamePlay.sessionID = @sessionID";
            comm.Parameters.AddWithValue("@sessionID", sessionID);

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                gamePlay p = new gamePlay();
                p.Uid = Convert.ToInt16(dr["uid"].ToString());
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                try
                {
                    p.JointID = Convert.ToInt16(dr["jointID"].ToString());
                }
                catch (Exception) { }
             
                p.GameKey = dr["gameKey"].ToString();
                p.GameValue = dr["gameValue"].ToString();

                result.Add(p);
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return result;
        }

        /// <summary>
        /// Retrieve gameplay by gamePlayID
        /// </summary>
        /// <param name="gamePlayID"></param>
        /// <returns></returns>
        public gamePlay retrieveGamePlay(int gamePlayID)
        {
            gamePlay p = new gamePlay();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM  gamePlay WHERE uid = @uid";
            comm.Parameters.AddWithValue("@uid", gamePlayID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                p.Uid = Convert.ToInt16(dr["uid"].ToString());
                try
                {
                    p.StageID = Convert.ToInt16(dr["stageID"].ToString());
                }
                catch (Exception) { }
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                p.SpecialInstructions = dr["specialInstructions"].ToString();
                p.JointID = Convert.ToInt16(dr["joint"].ToString());
                p.GameKey = dr["gameKey"].ToString();
                p.GameValue = dr["gameValue"].ToString();
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            return p;
        }

        /// <summary>
        /// Retrieve the first gamplay ID with respect to exeID and patientID
        /// </summary>
        /// <param name="exeID"></param>
        /// <param name="patientID"></param>
        /// <returns></returns>
        public List<KeyValuePair<string, string>> getOldestUID(int exeID, int patientID)
        {
            List<KeyValuePair<string, string>> tempList = new List<KeyValuePair<string, string>>();
            KeyValuePair<string, string> temp = new KeyValuePair<string, string>();

            //if (uid != 0)
            //{
            SqlOpenConnection();
            string custKey;
            string custValue;
            SqlCommand comm = SqlCommandConnection();
            //comm.CommandText = "Select gameKey, gameValue from gamePlay where sessionID=(Select top 1 sessionID from session where exeID=@exeID and patientID=@patientID and dateofplay=(select min(dateofplay) from session where exeID=@exeID and patientID=@patientID))";
            comm.CommandText = "Select gameKey, gameValue from gamePlay where sessionID=(Select top 1 sessionID from session where  exeID=@exeID and patientID=@patientID order by dateofplay asc)";
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@patientID", patientID);

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                custKey = dr["gameKey"].ToString();
                custValue = dr["gameValue"].ToString();
                if (custKey.Contains("Average Time"))
                {
                    temp = new KeyValuePair<string, string>(custKey, custValue);
                    tempList.Add(temp);
                }
            }

            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            //}
            return tempList;
        }

        public List<gamePlay> getMaxMinAngle(int patientID)
        {
            List<gamePlay> gpList = new List<gamePlay>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM gamePlay WHERE sessionID = (select top 1 sessionID from session where patientID =@uid and exeID = 10 order by dateofplay desc)";
            comm.Parameters.AddWithValue("@uid", patientID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                gamePlay gp = new gamePlay();
                gp.Uid = Convert.ToInt16(dr["uid"].ToString());
                gp.JointID = Convert.ToInt16(dr["jointID"].ToString());
                gp.GameKey = dr["gameKey"].ToString();
                gp.GameValue = dr["gameValue"].ToString();
                gpList.Add(gp);
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            return gpList;
        }

        //public List<gamePlay> retrievePatientAllGamePlay(int patientID)
        //{
        //    List<gamePlay> result = new List<gamePlay>();
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    //comm.CommandText = "SELECT TOP 1 * FROM  gamePlay WHERE patientID = @patientID order by dateofplay DESC";
        //    comm.CommandText = "SELECT * FROM  gamePlay, exe WHERE exe.uid = gamePlay.exeID AND gamePlay.patientID = @patientID order by dateofplay DESC";
        //    comm.Parameters.AddWithValue("@patientID", patientID);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        gamePlay p = new gamePlay();
        //        if (p.ExeID != 1)
        //        {
        //            p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
        //            p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
        //            p.Name = dr["name"].ToString();
        //            try
        //            {
        //                p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
        //                p.Points = dr["points"].ToString();
        //                p.Rounttime = dr["roundtime"].ToString();
        //            }
        //            catch (Exception) { }
        //            p.Uid = Convert.ToInt16(dr["uid"].ToString());
        //            p.SpecialInstructions = dr["specialInstructions"].ToString();
        //            p.AvgTime1 = dr["avgTime1"].ToString();
        //            p.AvgTime2 = dr["avgTime2"].ToString();
        //            p.AvgTime3 = dr["avgTime3"].ToString();
        //            p.AvgTime4 = dr["avgTIme4"].ToString();
        //            result.Add(p);
        //        }
        //    }
        //    SqlCloseConnection();
        //    return result;
        //}

        //public List<gamePlay> retrievePatientJointGamePlay(int patientID, int joint)
        //{
        //    List<gamePlay> result = new List<gamePlay>();
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    //comm.CommandText = "SELECT TOP 1 * FROM  gamePlay WHERE patientID = @patientID order by dateofplay DESC";
        //    comm.CommandText = "SELECT * FROM  gamePlay, exe, exe_tag WHERE exe.uid = gamePlay.exeID AND exe_tag.exeID = exe.uid AND exe_tag.joint = @joint AND gamePlay.patientID = @patientID order by dateofplay DESC";
        //    comm.Parameters.AddWithValue("@patientID", patientID);
        //    comm.Parameters.AddWithValue("@joint", joint);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        gamePlay p = new gamePlay();

        //        p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
        //        p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
        //        p.Name = dr["name"].ToString();
        //        try
        //        {
        //            p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
        //            p.Points = dr["points"].ToString();
        //            p.Rounttime = dr["roundtime"].ToString();
        //        }
        //        catch (Exception) { }
        //        p.Uid = Convert.ToInt16(dr["uid"].ToString());
        //        p.SpecialInstructions = dr["specialInstructions"].ToString();
        //        p.AvgTime1 = dr["avgTime1"].ToString();
        //        p.AvgTime2 = dr["avgTime2"].ToString();
        //        p.AvgTime3 = dr["avgTime3"].ToString();
        //        p.AvgTime4 = dr["avgTIme4"].ToString();
        //        result.Add(p);

        //    }
        //    SqlCloseConnection();
        //    return result;
        //}

        //public List<gamePlay> retrievePatientJointGamePlayByEXE(int patientID, int joint, int exeID)
        //{
        //    List<gamePlay> result = new List<gamePlay>();
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    //comm.CommandText = "SELECT TOP 1 * FROM  gamePlay WHERE patientID = @patientID order by dateofplay DESC";
        //    comm.CommandText = "SELECT * FROM  gamePlay, exe, exe_tag WHERE exe.uid = gamePlay.exeID AND exe_tag.exeID = exe.uid AND exe_tag.joint = @joint AND gamePlay.patientID = @patientID  AND exe.uid = @exeID order by dateofplay DESC";
        //    comm.Parameters.AddWithValue("@patientID", patientID);
        //    comm.Parameters.AddWithValue("@joint", joint);
        //    comm.Parameters.AddWithValue("@exeID", exeID);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        gamePlay p = new gamePlay();

        //        p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
        //        p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
        //        p.Name = dr["name"].ToString();
        //        try
        //        {
        //            p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
        //            p.Points = dr["points"].ToString();
        //            p.Rounttime = dr["roundtime"].ToString();
        //        }
        //        catch (Exception) { }
        //        p.Uid = Convert.ToInt16(dr["uid"].ToString());
        //        p.SpecialInstructions = dr["specialInstructions"].ToString();
        //        p.AvgTime1 = dr["avgTime1"].ToString();
        //        p.AvgTime2 = dr["avgTime2"].ToString();
        //        p.AvgTime3 = dr["avgTime3"].ToString();
        //        p.AvgTime4 = dr["avgTIme4"].ToString();
        //        result.Add(p);

        //    }
        //    SqlCloseConnection();
        //    return result;
        //}

        //public List<gamePlay> retrievePatientMostRecentGamePlay(int patientID, int number)
        //{
        //    List<gamePlay> result = new List<gamePlay>();
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    //comm.CommandText = "SELECT TOP 1 * FROM  gamePlay WHERE patientID = @patientID order by dateofplay DESC";
        //    comm.CommandText = "SELECT TOP @number * FROM  gamePlay, exe WHERE exe.uid = gamePlay.exeID AND gamePlay.patientID = @patientID order by dateofplay DESC";
        //    comm.Parameters.AddWithValue("@numer", number);
        //    comm.Parameters.AddWithValue("@patientID", patientID);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        gamePlay p = new gamePlay();
        //        if (p.ExeID != 1)
        //        {
        //            p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
        //            p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
        //            p.Name = dr["name"].ToString();
        //            p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
        //            p.Points = dr["points"].ToString();
        //            p.Rounttime = dr["roundtime"].ToString();
        //            p.Uid = Convert.ToInt16(dr["uid"].ToString());
        //            p.SpecialInstructions = dr["specialInstructions"].ToString();
        //            p.Anglemax = dr["anglemax"].ToString();
        //            p.Anglemin = dr["anglemin"].ToString();
        //            result.Add(p);
        //        };
        //    }
        //    SqlCloseConnection();
        //    return result;
        //}

        //public List<gamePlay> retrievePatientGamePlayofaEXE(int patientID, int exeID)
        //{
        //    List<gamePlay> result = new List<gamePlay>();
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    comm.CommandText = "SELECT * FROM  gamePlay WHERE patientID = @patientID AND exeID = @exeID order by dateofplay DESC";
        //    comm.Parameters.AddWithValue("@patientID", patientID);
        //    comm.Parameters.AddWithValue("@exeID", exeID);
        //    SqlDataReader dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        gamePlay p = new gamePlay();
        //        p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
        //        p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
        //        try
        //        {
        //            p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
        //            p.Points = dr["points"].ToString();
        //            p.Rounttime = dr["roundtime"].ToString();
        //        }
        //        catch (Exception) { }
        //        p.Uid = Convert.ToInt16(dr["uid"].ToString());
        //        p.Anglemax = dr["anglemax"].ToString();
        //        p.Anglemin = dr["anglemin"].ToString();
        //        p.Joint = dr["joint"].ToString();
        //        p.AvgTime1 = dr["avgTime1"].ToString();
        //        p.AvgTime2 = dr["avgTime2"].ToString();
        //        p.AvgTime3 = dr["avgTime3"].ToString();
        //        p.AvgTime4 = dr["avgTIme4"].ToString();
        //        result.Add(p);
        //    }
        //    SqlCloseConnection();
        //    return result;
        //}

        #endregion

        # region CRUD for session

        /// <summary>
        /// New session each time the patient play
        /// </summary>
        /// <param name="patientID"></param>
        /// <param name="exeID"></param>
        /// <returns></returns>
        public int createNewSession(int patientID, int exeID)
        {
            DateTime dateofplay = DateTime.Now;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO session(patientID,exeID,dateofplay) Values (@patientID , @exeID , @dateofplay);" + "Select Scope_Identity() ";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@dateofplay", dateofplay);

            int id = Convert.ToInt32(comm.ExecuteScalar());
            SqlCloseConnection();
            return id;
        }

        /// <summary>
        /// Get latest Session of a patient and EXE
        /// </summary>
        /// <param name="patientID"></param>
        /// <param name="exeID"></param>
        /// <returns></returns>
        public int retrieveMostRecentSessionIDOfPatientofExe(int patientID, int exeID)
        {
            int result = 0;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT TOP 1 * FROM  session WHERE patientID = @patientID AND exeID = @exeID ORDER BY dateofplay DESC";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = Convert.ToInt16(dr["sessionID"].ToString());
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            return result;
        }

        public DateTime retrieveLatestAssessmentDate(int patientID)
        {
            DateTime result = DateTime.Now;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT TOP 1 sessionID FROM session WHERE patientID = @patientID AND exeID = @exeID ORDER BY dateofplay DESC";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@exeID", 2);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = Convert.ToDateTime(dr["dateofplay"].ToString());
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            return result;
        }

        //public List<joint> getMaxMin(int patientID)
        //{
        //    SqlOpenConnection();
        //    SqlCommand comm = SqlCommandConnection();
        //    comm.CommandText = "select  SELECT TOP 1 sessionID FROM session WHERE patientID = @patientID AND exeID = @exeID ORDER BY dateofplay DESC";
        //    comm.Parameters.AddWithValue("@patientID", patientID);
        //    comm.Parameters.AddWithValue("@exeID", 10);
        //}

        public DateTime retrieveLatestGamePlayDate(int patientID)
        {
            DateTime result = DateTime.Now;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT TOP 1 * FROM session WHERE patientID = @patientID AND exeID = @exeID ORDER BY dateofplay DESC";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@exeID", 1);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = Convert.ToDateTime(dr["dateofplay"].ToString());
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            return result;
        }

        /// <summary>
        /// Retrieve session for patient
        /// </summary>
        /// <param name="patientID"></param>
        /// <returns></returns>
        public List<session> retrieveSessionOfPatient(int patientID)
        {
            List<session> result = new List<session>();
            List<session> result1 = new List<session>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM session WHERE patientID = @patientID ";
            comm.Parameters.AddWithValue("@patientID", patientID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                session p = new session();
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
                p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
                p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
                result.Add(p);
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();

            foreach (session s in result)
            {
                s.GamePlaylist = retrieveAllGamePlayofSession(s.SessionID);
                result1.Add(s);
            }
            return result1;
        }

        /// <summary>
        /// Get session detail
        /// </summary>
        /// <param name="patientID"></param>
        /// <param name="exeID"></param>
        /// <returns></returns>
        public List<session> retrieveSessionOfPatientofExe(int patientID, int exeID)
        {
            List<session> result = new List<session>();
            List<session> result1 = new List<session>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM session WHERE patientID = @patientID AND exeID = @exeID ";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@exeID", exeID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                session p = new session();
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
                p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
                p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
                result.Add(p);
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            foreach (session s in result)
            {
                s.GamePlaylist = retrieveAllGamePlayofSession(s.SessionID);
                result1.Add(s);
            }
            return result1;
        }

        /// <summary>
        /// Get session detail
        /// </summary>
        /// <param name="sessionID"></param>
        /// <returns></returns>
        public List<session> retrieveSession(int sessionID)
        {
            List<session> result = new List<session>();
            List<session> result1 = new List<session>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM  session WHERE sessionID = @sessionID ";
            comm.Parameters.AddWithValue("@sessionID", sessionID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                session p = new session();
                p.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                p.PatientID = Convert.ToInt16(dr["patientID"].ToString());
                p.ExeID = Convert.ToInt16(dr["exeID"].ToString());
                p.Dateofplay = Convert.ToDateTime(dr["dateofplay"].ToString());
                result.Add(p);
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            foreach (session s in result)
            {
                s.GamePlaylist = retrieveAllGamePlayofSession(s.SessionID);
                result1.Add(s);
            }
            return result1;
        }

        #endregion

        # region CRUD for Tag
        /// <summary>
        /// Retrieve jointname according to the jointID
        /// </summary>
        /// <param name="joint"></param>
        /// <returns></returns>
        public string retrieveJointName(string jointID)
        {
            string result = "";
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT jointName FROM tag WHERE joint = @joint";
            comm.Parameters.AddWithValue("@joint", jointID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = dr["jointName"].ToString();
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return result;
        }
        # endregion

        #region CRUD for problem
        public int addAndUpdateJointProblem(int jointID, int patientID, int severity)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "if EXISTS (Select * from problem where patientID = @patientID and jointid = @jointID) update problem set severity = @severity where jointid = @jointID and patientId = @patientID else insert into problem (patientID, jointid, severity) values (@patientID, @jointID, @severity)";
            comm.Parameters.AddWithValue("@jointID", jointID);
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@severity", severity);

            int id = Convert.ToInt32(comm.ExecuteScalar());
            SqlCloseConnection();
            return id;
        }

        private List<KeyValuePair<int, int>> getAllJoint(int patientID)
        {
            List<KeyValuePair<int, int>> tempList = new List<KeyValuePair<int, int>>();
            KeyValuePair<int, int> temp = new KeyValuePair<int, int>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select jointID, severity from problem where patientID =@patientID";
            comm.Parameters.AddWithValue("@patientID", patientID);

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                int jointID = Convert.ToInt16(dr["jointID"].ToString());
                int severity = Convert.ToInt16(dr["severity"].ToString());
                temp = new KeyValuePair<int, int>(jointID, severity);
                tempList.Add(temp);
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return tempList;
        }

        private List<joint> getAllJointM(int patientID)
        {
            List<joint> tempList = new List<joint>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select jointID, severity from problem where patientID =@patientID";
            comm.Parameters.AddWithValue("@patientID", patientID);

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                joint j = new joint();
                j.JointID = Convert.ToInt16(dr["jointID"].ToString());
                j.Severity = Convert.ToInt16(dr["severity"].ToString());
                tempList.Add(j);
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return tempList;
        }

        public bool removeJointProblem(int jointID, int patientID)
        {
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "delete from problem where jointID =@jointID and patientID =@patientID";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@jointID", jointID);

            int count = comm.ExecuteNonQuery();
            SqlCloseConnection();
            return count == 0 ? false : true;
        }
        #endregion

        #region score
        #region personal score
        public int retrievePersonalHighScore(int patientID, int exeID, int difficulty)
        {
            int result = 0;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select TOP 1 gameValue from gamePlay WHERE gamekey = 'Score (Points)' AND sessionID in " +
            "(SELECT sessionID FROM gamePlay WHERE gameKey = 'Difficulty (1-10)' " +
            "and gameValue = @difficulty " +
            "and sessionID in (SELECT sessionID FROM session " +
            "WHERE patientID = @patientID AND exeID = @exeID)) order by gameValue desc";
            comm.Parameters.AddWithValue("@patientID", patientID);
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@difficulty", difficulty);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                result = Convert.ToInt16(dr[0].ToString());
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();

            return result;
        }
        #endregion

        public List<highScore> retrieveHighScore(int exeID, int difficulty)
        {
            //get SessionID and gameValue
            List<highScore> resultUID = new List<highScore>();
            ArrayList nameList = new ArrayList();
            ArrayList list = getTopPlayers(difficulty, exeID);
            foreach (resultDB rdb in list)
            {
                nameList.Add(getPlayerNames(rdb.SessionID));
            }
            if (list.Count == nameList.Count)
            {
                for (int i = 0; i < nameList.Count; i++)
                {
                    highScore h = new highScore();
                    h.Name = nameList[i].ToString();
                    resultDB rdb = list[i] as resultDB;
                    h.Score = rdb.Score;
                    resultUID.Add(h);
                }
            }
            else
            { }
            return resultUID;
        }

        private ArrayList getTopPlayers(int difficulty, int exeID)
        {
            ArrayList al = new ArrayList();

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "(Select Top 10 sessionID,gameValue  from gamePlay WHERE gamekey = 'Score (Points)' AND sessionID in " +
            "(SELECT sessionID FROM gamePlay WHERE gameKey = 'Difficulty (1-10)' " +
            "and gameValue =  @difficulty " +
            "and sessionID in (SELECT sessionID FROM session " +
            "WHERE exeID = @exeID))) order by gameValue desc";
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@difficulty", difficulty);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                resultDB rdb = new resultDB();
                rdb.SessionID = Convert.ToInt16(dr[0].ToString());
                rdb.Score = Convert.ToInt16(dr[1].ToString());
                al.Add(rdb);
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            return al;
        }

        private string getPlayerNames(int rdb)
        {
            string name = "";
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select FirstName + LastName From patient WHERE uid in " +
            "(Select patientID from session WHERE sessionID = @sessionID)";
            comm.Parameters.AddWithValue("@sessionID", rdb);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                name = ((dr[0].ToString()));
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();
            return name;
        }
        #endregion

        #region Mobile APP
        public List<patientM> retrieveAllOwnPatientsM(int docID)
        {
            List<patientM> result = new List<patientM>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            try
            {
                comm.CommandText = "SELECT * FROM patient where doctorID=@doc";
                comm.Parameters.AddWithValue("@doc", docID);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    patientM p = new patientM();
                    p.Address1 = dr["Address1"].ToString();
                    p.Doc = Convert.ToInt32(dr["doctorID"].ToString());
                    p.FirstName = dr["FirstName"].ToString();
                    p.LastName = dr["LastName"].ToString();
                    p.Postcode = dr["Postcode"].ToString();
                    p.Uid = Convert.ToInt16(dr["uid"].ToString());
                    try
                    {
                        p.DateOfBirth = Convert.ToDateTime(dr["dateOfBirth"].ToString());
                    }
                    catch (Exception) { }
                    p.Email = dr["email"].ToString();
                    p.Gender = dr["gender"].ToString();
                    p.ContactNumHome = dr["contactNumHome"].ToString();
                    p.ContactNumMob = dr["contactNumMobile"].ToString();
                    p.Country = dr["country"].ToString();
                    //p.ImagePath = dr["faceImgPath"].ToString();
                    p.ImageByte = retrieveImage(dr["faceImgPath"].ToString());
                    p.Nric = dr["nric"].ToString();
                    p.AdminDate = Convert.ToDateTime(dr["adminDate"].ToString());
                    result.Add(p);
                }
                dr.Close();
                dr.Dispose();
                SqlCloseConnection();
                foreach (patientM temp in result)
                {
                    temp.JointSeverity = getAllJointM(temp.Uid);
                }
            }
            catch (SqlException)
            {
                return null;
            }
            return result;
        }

        public doctor doctorLoginM(string username, string password)
        {
            doctor d = new doctor();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "select * from doc_login where userAccount = @userAccount";
            comm.Parameters.AddWithValue("@userAccount", username);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                string Pwd = dr["password"].ToString();
                if (Pwd == Hash(password))
                {
                    d.Uid = Convert.ToInt16((dr["uid"]));
                    d.FirstName = dr["firstName"].ToString();
                    d.LastName = dr["lastName"].ToString();
                    d.OfficeFax = dr["officeFax"].ToString();
                    d.OfficeTel = dr["officeTel"].ToString();
                    d.ImageByte = retrieveImage(dr["faceImgPath"].ToString());
                    return d;
                }
                else
                {
                    return null;
                }
            }
            return null;
        }

        public patientM patientLoginM(string username, string password)
        {
            patientM p = new patientM();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "select * from patient where nric = @nric";
            comm.Parameters.AddWithValue("@nric", username);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                string Pwd = (string)(dr["password"]);
                if (Pwd == Hash(password))
                {
                    p.Address1 = dr["Address1"].ToString();
                    p.Doc = Convert.ToInt32(dr["doctorID"].ToString());
                    p.FirstName = dr["FirstName"].ToString();
                    p.LastName = dr["LastName"].ToString();
                    p.Postcode = dr["Postcode"].ToString();
                    p.Uid = Convert.ToInt16(dr["uid"].ToString());
                    try
                    {
                        p.DateOfBirth = Convert.ToDateTime(dr["dateOfBirth"].ToString());
                    }
                    catch (Exception) { }
                    p.Email = dr["email"].ToString();
                    p.Gender = dr["gender"].ToString();
                    p.ContactNumHome = dr["contactNumHome"].ToString();
                    p.ContactNumMob = dr["contactNumMobile"].ToString();
                    p.Country = dr["country"].ToString();
                    //p.ImagePath = dr["faceImgPath"].ToString();
                    p.ImageByte = retrieveImage(dr["faceImgPath"].ToString());
                    p.Nric = dr["nric"].ToString();
                    p.AdminDate = Convert.ToDateTime(dr["adminDate"].ToString());
                    return p;
                }
                else
                {
                    return null;
                }
            }
            return null;
        }

        public doctor retrievePatientDoctor(int uid)
        {
            doctor p = new doctor();
            int docID = 0;
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            //try
            //{
            comm.CommandText = "SELECT doctorID FROM patient where uid = @uid";
            comm.Parameters.AddWithValue("@uid", uid);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                docID = Convert.ToInt16(dr["doctorID"].ToString());
                p = getDoctor(docID);
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return p;
        }

        public doctor getDoctor(int uid)
        {
            doctor d = new doctor();

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            //try
            //{
            comm.CommandText = "SELECT * FROM doc_login where uid = @uid";
            comm.Parameters.AddWithValue("@uid", uid);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                d.Uid = Convert.ToInt16((dr["uid"]));
                d.FirstName = dr["firstName"].ToString();
                d.LastName = dr["lastName"].ToString();
                d.OfficeFax = dr["officeFax"].ToString();
                d.OfficeTel = dr["officeTel"].ToString();
                d.ImageByte = retrieveImage(dr["faceImgPath"].ToString());
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return d;
        }

        public patientM retrievePatientM(int uid)
        {
            patientM p = new patientM();

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            //try
            //{
            comm.CommandText = "SELECT * FROM patient where uid = @uid";
            comm.Parameters.AddWithValue("@uid", uid);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                p.Address1 = dr["Address1"].ToString();
                p.Doc = Convert.ToInt32(dr["doctorID"].ToString());
                p.FirstName = dr["FirstName"].ToString();
                p.LastName = dr["LastName"].ToString();
                p.Postcode = dr["Postcode"].ToString();
                p.Uid = Convert.ToInt16(dr["uid"].ToString());
                try
                {
                    p.DateOfBirth = Convert.ToDateTime(dr["dateOfBirth"].ToString());
                }
                catch (Exception) { }
                p.Email = dr["email"].ToString();
                p.Gender = dr["gender"].ToString();
                p.ContactNumHome = dr["contactNumHome"].ToString();
                p.ContactNumMob = dr["contactNumMobile"].ToString();
                p.Country = dr["country"].ToString();
                //p.ImagePath = dr["faceImgPath"].ToString();
                p.ImageByte = retrieveImage(dr["faceImgPath"].ToString());
                p.Nric = dr["nric"].ToString();
                p.AdminDate = Convert.ToDateTime(dr["adminDate"].ToString());
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            p.JointSeverity = getAllJointM(p.Uid);
            return p;
        }

        public List<gamePlay> getAllMaxMinAngleM(int patientID)
        {
            List<gamePlay> gpList = new List<gamePlay>();
            List<int> sessionsID = retrieveSessionOfPatientEXE10M(patientID);
            foreach (int i in sessionsID)
            {
                SqlOpenConnection();
                SqlCommand comm = SqlCommandConnection();
                comm.CommandText = "SELECT * FROM gamePlay WHERE sessionID = @sessionID";
                comm.Parameters.AddWithValue("@sessionID", i);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    gamePlay gp = new gamePlay();
                    gp.Uid = Convert.ToInt16(dr["uid"].ToString());
                    gp.JointID = Convert.ToInt16(dr["jointID"].ToString());
                    gp.SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                    gp.GameKey = dr["gameKey"].ToString();
                    gp.GameValue = dr["gameValue"].ToString();
                    gpList.Add(gp);
                }
                dr.Close(); dr.Dispose();
                SqlCloseConnection();
            }
            return gpList;
        }

        public List<int> retrieveSessionOfPatientEXE10M(int patientID)
        {
            List<int> result = new List<int>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT * FROM session WHERE patientID = @patientID and exeID = 10";
            comm.Parameters.AddWithValue("@patientID", patientID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                int SessionID = Convert.ToInt16(dr["sessionID"].ToString());
                result.Add(SessionID);
            }
            dr.Close(); dr.Dispose();
            SqlCloseConnection();

            return result;
        }

        #endregion

        #region SMS
        public KeyValuePair<string, string> retrieveDoctorNumber(string patientNumber)
        {
            //List<KeyValuePair<string, string>> tempList = new List<KeyValuePair<string, string>>();
            KeyValuePair<string, string> temp = new KeyValuePair<string, string>();

            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select firstName, lastName, doctorID from patient where contactNumMobile =@patientNumber";
            comm.Parameters.AddWithValue("@patientNumber", patientNumber);

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                string firstName = dr["firstName"].ToString();
                string lastName = dr["lastName"].ToString();
                string doctorID = dr["doctorID"].ToString();
                temp = new KeyValuePair<string, string>(firstName + " " + lastName, getDoctorHPNum(Convert.ToInt32(doctorID)));
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return temp;
        }

        private string getDoctorHPNum(int docID)
        {
            string number = "";
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select officeTel from doc_login where uid =@docID";
            comm.Parameters.AddWithValue("@docID", docID);

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                number = dr["officeTel"].ToString();
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return number;
        }

        private string getPatientHP(int patientID)
        {
            string num = "";
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select contactNumMobile from patient where uid =@patientID";
            comm.Parameters.AddWithValue("@patientID", patientID);

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                num = dr["contactNumMobile"].ToString();
                return num;
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return null;
        }

        public void smsTechnicalSupport(int patientID, string msg)
        {
            WebRole.SendMessageToTechnicalSupport(msg, 91151435, getPatientHP(patientID));
        }

        public void smsDoctor(int patientID, string msg)
        {
            KeyValuePair<string, string> docHPName = retrieveDoctorNumber(patientID.ToString());
            WebRole.SendMessageToDoctorSupport(msg, Convert.ToInt32(docHPName.Value), getPatientHP(patientID).ToString(), docHPName.Key.ToString());
        }

        #endregion

        #region teleRehab

        //public void getLatestUID(int exeID)
        //{
        //SqlOpenConnection();
        //int uid = 0;
        //SqlCommand comm = SqlCommandConnection();
        //comm.CommandText = "Select top 1 uid from patient_exe where exe=@exeID and patient=@patientID and dateassigned=(select max(dateassigned) from patient_exe where exe=@exeID and patient=@patientID)";
        //comm.Parameters.AddWithValue("@exeID", exeID);
        //comm.Parameters.AddWithValue("@patientID", patientID);

        //SqlDataReader dr = comm.ExecuteReader();
        //while (dr.Read())
        //{
        //    uid = Convert.ToInt32(dr["uid"].ToString());
        //}
        //dr.Close();
        //dr.Dispose();
        //CloseConnection();
        //List<KeyValuePair<string, string>> tempList = new List<KeyValuePair<string, string>>();
        //KeyValuePair<string, string> temp = new KeyValuePair<string, string>();

        //if (uid != 0)
        //{
        //    SqlOpenConnection();
        //    string custKey;
        //    string custValue;
        //    comm = SqlCommandConnection();
        //    comm.CommandText = "Select custKey, custValue from patient_exe_custKey where patient_exe_id=@uid";
        //    comm.Parameters.AddWithValue("@uid", uid);

        //    dr = comm.ExecuteReader();
        //    while (dr.Read())
        //    {
        //        custKey = dr["custKey"].ToString();
        //        custValue = dr["custValue"].ToString();
        //        temp = new KeyValuePair<string, string>(custKey, custValue);
        //        tempList.Add(temp);
        //    }

        //    dr.Close();
        //    dr.Dispose();
        //    CloseConnection();
        //}
        //return tempList;
        //}

        public List<KeyValuePair<string, string>> getLatestUID(int exeID, int patientID)
        {
            List<KeyValuePair<string, string>> tempList = new List<KeyValuePair<string, string>>();
            KeyValuePair<string, string> temp = new KeyValuePair<string, string>();
            SqlOpenConnection();
            string custKey;
            string custValue;
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select custKey, custValue from patient_exe_custKey where patient_exe_id=(Select top 1 uid from patient_exe where exeID=@exeID and patientID=@patientID and dateassigned=(select max(dateassigned) from patient_exe where exeID=@exeID and patientID=@patientID))";
            //comm.Parameters.AddWithValue("@uid", uid);
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@patientID", patientID);

            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                custKey = dr["custKey"].ToString();
                custValue = dr["custValue"].ToString();
                temp = new KeyValuePair<string, string>(custKey, custValue);
                tempList.Add(temp);
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return tempList;
        }

        public List<double> getLatestAngle(int exeID, int patientID, int jointID, string axis)
        {
            List<double> value = new List<double>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "Select sessionID from session where sessionid = (select max(sessionid) from session where patientid=@patientID and exeID=@exeID)";
            //int sessionid = 0;
            //comm.Parameters.AddWithValue("@uid", uid);
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@patientID", patientID);
            int sessionid = 0;
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                sessionid = Convert.ToInt32(dr["sessionID"].ToString());
                //temp = new KeyValuePair<string, string>(custKey, custValue);
                //tempList.Add(temp);
            }
            dr.Close();
            comm.CommandText = "select gameValue from gameplay where sessionID = @sessionID and joint = @jointID and gamekey = @gameKey";
            comm.Parameters.AddWithValue("@sessionID", sessionid);
            comm.Parameters.AddWithValue("@jointID", jointID);
            comm.Parameters.AddWithValue("@gameKey", ("Max Angle " + axis).Trim());

            double maxValue = 0.0;
            dr = comm.ExecuteReader();
            while (dr.Read())
            {
                maxValue = Convert.ToInt32(dr["gameValue"].ToString());
                //temp = new KeyValuePair<string, string>(custKey, custValue);
                //tempList.Add(temp);
            }

            dr.Close();
            comm.Parameters.Clear();
            comm.CommandText = "select gameValue from gameplay where sessionID = @sessionID and joint = @jointID and gamekey = @gameKey";
            comm.Parameters.AddWithValue("@sessionID", sessionid);
            comm.Parameters.AddWithValue("@jointID", jointID);
            comm.Parameters.AddWithValue("@gameKey", ("Min Angle " + axis).Trim());
            double minValue = 0.0;
            dr = comm.ExecuteReader();
            while (dr.Read())
            {
                minValue = Convert.ToInt32(dr["gameValue"].ToString());
                //temp = new KeyValuePair<string, string>(custKey, custValue);
                //tempList.Add(temp);
            }

            value.Add(maxValue);
            value.Add(minValue);
            dr.Dispose();
            SqlCloseConnection();


            return value;
        }

        public void saveScreenShot(int patientID, int exeID, string fileName)
        {
            DateTime datetime = DateTime.Now;
            //string imgPath = uploadFaceImage((patientID + "_" + 1).ToString(), fileBtyes);
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "INSERT INTO exe_ScreenCapture (ssPath, exeID, dateTime, patientID) Values (@ssPath, @exeID, @dateTime, @patientID)";
            comm.Parameters.AddWithValue("@dateTime", datetime);
            comm.Parameters.AddWithValue("@ssPath", fileName);
            comm.Parameters.AddWithValue("@exeID", exeID);
            comm.Parameters.AddWithValue("@patientID", patientID);

            int result = comm.ExecuteNonQuery();
            SqlCloseConnection();

            // create_exe_custKey

            //return result == 0 ? false : true;
        }

        public List<byte[]> readScreenShot(int patientID)
        {
            List<byte[]> temp = new List<byte[]>();
            SqlOpenConnection();
            SqlCommand comm = SqlCommandConnection();
            comm.CommandText = "SELECT Top 5 * FROM exe_ScreenCapture where patientID=@patientID";
            comm.Parameters.AddWithValue("@patientID", patientID);
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                byte[] image = retrieveImage(dr["ssPath"].ToString());
                temp.Add(image);
            }
            dr.Close();
            dr.Dispose();
            SqlCloseConnection();
            return temp;
        }

        #endregion
    }
}